package com.neuedu.utils;



import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class DbUtil {
    public static final String RUNNING_MODE_DEBUG="DEBUG";
    public static final String RUNNING_MODE_RELEASE="RELEASE";
    public static final String RUNNING_MODE=RUNNING_MODE_DEBUG;
    public static final String DATABASE_NAME="wuye";
    public static final String DATABASE_PORT="3306";
    public static final String DATABASE_ADDRESS_IP="localhost";
    public static final String USERNAME="root";
    public static final String PASSWORD="123456";
    public static final String DATABASE_DRIVER_CLASS="com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql://"+DATABASE_ADDRESS_IP+":"+DATABASE_PORT+"/"+DATABASE_NAME+"?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";

    static{
        try {
            Class.forName(DATABASE_DRIVER_CLASS);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){

        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }

    public static void close(Statement s,Connection conn){
        if(s!=null){
            try {
                s.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }


    public static void close(ResultSet rs, Statement s,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        close(s,conn);
    }

    private static String fields2Sql(Field[] fields){
        String sql="SELECT ";
        for (int i = 0; i < fields.length; i++) {
            //设置可见性
            fields[i].setAccessible(true);
            String colName = fields[i].getName();
            colName=swap(colName).toUpperCase();
            sql+=colName;
            if(i!=fields.length-1){
                sql+=",";
            }
        }
        return sql;
    }

    private static <T> List<T> resultSet2List(Class <T> cls,ResultSet rs,Field [] fields) throws SQLException, IllegalAccessException, InstantiationException {
        List<T> result=new ArrayList<>();
        while (rs.next()){
            //newInstance()就是调用这个cls指向的类的无参构造方法
            T object=cls.newInstance();
            //一行数据的提取
            for (int i = 0; i < fields.length; i++) {
                Object value=rs.getObject(swap(fields[i].getName()).toUpperCase());
                //给object的fieldsp[i]指向的成员赋值为value
                fields[i].set(object,value);
            }
            result.add(object);
        }
        return result;
    }


    public static <T> int insertSelective(T object){
        Class<?> cls = object.getClass();
        String tableName = cls.getSimpleName();
        String sql="INSERT INTO "+swap(tableName);
        Field[] fields = cls.getDeclaredFields();

        String colNames="(";
        String colValues="(";
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            Object value = null;
            try {
                value = fields[i].get(object);
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if(value==null){
                continue;
            }
            colNames=colNames+swap(fields[i].getName())+",";
            colValues=colValues+"?"+",";
        }
        colNames=colNames.substring(0,colNames.length()-1)+")";
        colValues=colValues.substring(0,colValues.length()-1)+")";
        sql=sql+colNames+" VALUES "+colValues;
        Connection conn=getConnection();
        PreparedStatement ps =null;
        try {

            ps= conn.prepareStatement(sql);
            int n=0;
            for (int i = 0; i < fields.length; i++) {
                Object o = fields[i].get(object);
                if(o==null){
                    n++;
                    continue;
                }
                ps.setObject(i+1-n,o);
            }
            if(RUNNING_MODE.equals(RUNNING_MODE_DEBUG)){
                System.out.println(sql);
            }
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException | IllegalAccessException throwables) {
            throwables.printStackTrace();
        }finally {
            close(ps,conn);
        }
        return 0;
    }

    /**
     * insert into dept(deptno,dname,loc) values (?,?,?)
     */
    public static <T> int insert(T object){
        Class<?> cls = object.getClass();
        String tableName = cls.getSimpleName();
        String sql="INSERT INTO "+tableName;
        Field[] fields = cls.getDeclaredFields();

        String colNames="(";
        String colValues="(";
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            colNames=colNames+fields[i].getName();
            if(i!=fields.length-1){
                colNames=colNames+",";
            }
            colValues=colValues+"?";
            if(i!=fields.length-1){
                colValues=colValues+",";
            }
        }
        colNames=colNames+")";
        colValues=colValues+")";
        sql=sql+colNames+" VALUES "+colValues;
        Connection conn=getConnection();
        PreparedStatement ps =null;
        try {
            ps= conn.prepareStatement(sql);
            for (int i = 0; i < fields.length; i++) {
                ps.setObject(i+1,fields[i].get(object));
            }
            if(RUNNING_MODE.equals(RUNNING_MODE_DEBUG)){
                System.out.println(sql);
            }
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException | IllegalAccessException throwables) {
            throwables.printStackTrace();
        }finally {
           close(ps,conn);
        }
        return 0;
    }

    /**
     * delete from dept where xxx=?
     */
    public static <T> int delete(Class<T> cls,String where,Object ... args){
        String tableName = cls.getSimpleName();
        String sql="DELETE FROM "+swap(tableName)+" WHERE "+where;
        Connection conn=getConnection();
        PreparedStatement ps =null;
        try {
            ps=conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(ps,conn);
        }
        return 0;
    }




    /**
     * UPDATE dept SET deptno=?, dname=?, loc=? WHERE deptno=?;
     */
    public static <T> int update(T object,String where,Object ... args){
        Class<?> cls = object.getClass();
        String tableName = cls.getSimpleName();
        Field[] fields = cls.getDeclaredFields();
        String sql="UPDATE "+swap(tableName)+" SET ";
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            try {
                String colName = fields[i].getName();
                Object colValue = fields[i].get(object);
                if(colValue!=null){
                    sql=sql+swap(colName)+"=?,";
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        sql=sql.substring(0,sql.length()-1)+" WHERE "+where;
        Connection conn=getConnection();
        PreparedStatement ps =null;
        try {
            ps = conn.prepareStatement(sql);
            int n=0;
            int i = 0;
            for (; i < fields.length; i++) {
                Object colValue = fields[i].get(object);
                if(colValue==null){
                    n++;
                    continue;
                }
                ps.setObject(i+1-n,colValue);
            }

            for (int j = 0; j < args.length; j++) {
                ps.setObject(j+1+i-n,args[j]);
            }
            if(RUNNING_MODE.equals(RUNNING_MODE_DEBUG)){
                System.out.println(sql);
            }
            int result = ps.executeUpdate();
            return result;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            close(ps,conn);
        }

        return 0;
    }



    /**
     *
     * @param cls
     * @param where id=? and age=? or sex=?
     * @param args {1,18,"男"}
     * @param <T>
     * @return
     */

    public static <T> List<T> select(Class<T> cls,String where,Object ... args){
        Field[] fields = cls.getDeclaredFields();
        String sql = fields2Sql(fields);
        sql+=" FROM "+swap(cls.getSimpleName()).toUpperCase();
        sql+=" WHERE "+where.toUpperCase()+";";

        Connection conn=getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            ps=conn.prepareStatement(sql);

            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1,args[i]);
            }
            if(RUNNING_MODE.equals(RUNNING_MODE_DEBUG)){
                System.out.println(sql);
            }
            rs= ps.executeQuery();
            return resultSet2List(cls,rs,fields);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return null;
    }
    public static <T> List<T> select(Class<T> cls){
        Field[] fields = cls.getDeclaredFields();
        String sql = fields2Sql(fields);
        sql+=" FROM "+swap(cls.getSimpleName()).toUpperCase()+";";
        Connection conn=getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        try {
            ps=conn.prepareStatement(sql);
            if(RUNNING_MODE.equals(RUNNING_MODE_DEBUG)){
                System.out.println(sql);
            }
            rs= ps.executeQuery();
            return resultSet2List(cls,rs,fields);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }finally {
            close(rs,ps,conn);
        }
        return null;
    }




    /**
     * method description: 这个方法是用于转换字符串的。
     * 具体规则是：当入参@str里面有大写字母的时候，就要在大写字母(第一个字母除外)前面添加一个下划线。
     * 每个字母都如此。
     * 例如：@str="CreateSchoolTime" 则返回值为 "Create_School_Time"
     * @param str
     * @return
     */
    private static String swap(String str){
        String s="";
        for (int i = 0 ; i < str.length(); i++) {
            char c = str.charAt(i);
            if (c>='A'&&c<='Z'&&i!=0){
                s=s+"_"+c;
            }else {
                s=s+c;
            }
        }
        return s;
    }

}
